﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using System.Data.Linq;

namespace PersonaLibrary.Excel
{
    internal sealed class MainImport
    {
        Data.LinqToDbDataContext context = new Data.LinqToDbDataContext();

        Table<Data.Job> jobTable = null;
        Table<Data.Employee> emplTable = null;
        Table<Data.Department> depTable = null;
        Table<Data.Organization> orgTable = null;
        Table<Data.ConnectionJobEmplDep> relTable = null;

        public MainImport()
        {
            jobTable = context.GetTable<Data.Job>();
            emplTable = context.GetTable<Data.Employee>();
            depTable = context.GetTable<Data.Department>();
            orgTable = context.GetTable<Data.Organization>();
            relTable = context.GetTable<Data.ConnectionJobEmplDep>();

            OpenFileDialog oFD = new OpenFileDialog();
            
            if (oFD.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + oFD.FileName + ";Extended Properties=Excel 8.0");
                con.Open();

                System.Data.DataTable dt = null;

                dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + dt.Rows[0]["TABLE_NAME"].ToString() + "]", con);

                OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

                objAdapter1.SelectCommand = objCmdSelect;

                DataSet objDataset1 = new DataSet();

                objAdapter1.Fill(objDataset1);

                
                foreach(DataRow row in objDataset1.Tables[0].Rows)                
                    CreateOrganizations(row);

                foreach (DataRow row in objDataset1.Tables[0].Rows)
                    LinkOrg(row);

                foreach (DataRow row in objDataset1.Tables[0].Rows)
                    CreateDepartament(row);

                foreach (DataRow row in objDataset1.Tables[0].Rows)
                    CreateEmploee(row);

                foreach (DataRow row in objDataset1.Tables[0].Rows)
                    CreateJob(row);
                
                foreach (DataRow row in objDataset1.Tables[0].Rows)
                    CreateRel(row);


                con.Close();
            }
        }

        public void CreateOrganizations(DataRow row)
        {
            if (orgTable.Any(x => x.Name.ToString() == row[6].ToString())) return;

            Data.Organization org = new Data.Organization();
            org.Name = row[6].ToString();
            org.Category = row[7].ToString();
            org.Ownership = row[5].ToString();
            org.Site = row[21].ToString();

            orgTable.InsertOnSubmit(org);

            context.SubmitChanges();
        }

        public void LinkOrg(DataRow row)
        {
            if (string.IsNullOrEmpty(row[20].ToString())) return;

            if (!orgTable.Any(x => x.Name.ToString() == row[20].ToString())) return;

            var org = orgTable.Single(x => x.Name.ToString() == row[6].ToString());
            var parentOrg = orgTable.Single(x => x.Name.ToString() == row[20].ToString());

            org.ParentOrganization = parentOrg.Id;

            context.SubmitChanges();
        }

        public void CreateDepartament(DataRow row)
        {
            if (depTable.Any(x => x.Name.ToString() == row[8].ToString() && x.OrganizationId == orgTable.Single(y => y.Name.ToString() == row[6].ToString()).Id))                
                return;

            Data.Department dep = new Data.Department();
            dep.Address = row[17].ToString();
            dep.Email = row[16].ToString();
            dep.Name = row[8].ToString();
            dep.Office = row[15].ToString();
            dep.Telephone = "(" + row[11].ToString() + ") " + row[12].ToString();
            dep.OrganizationId = orgTable.Single(y => y.Name.ToString() == row[6].ToString()).Id;

            depTable.InsertOnSubmit(dep);

            context.SubmitChanges();
        }

        public void CreateEmploee(DataRow row)
        {
            if (emplTable.Any(x => x.Name.ToString() == row[2].ToString() && x.Patronymic.ToString() == row[3].ToString() && x.Surname.ToString() == row[1].ToString()))
                return;

            Data.Employee em = new Data.Employee();
            DateTime tryResult = new DateTime();
            if (DateTime.TryParse(row[4].ToString(), out tryResult)) em.Birthday = tryResult;
            em.Name = row[2].ToString();
            em.Patronymic = row[3].ToString();
            em.PersonalPhone = row[13].ToString();
            em.Surname = row[1].ToString();

            emplTable.InsertOnSubmit(em);

            context.SubmitChanges();
        }

        public void CreateJob(DataRow row)
        {
            if (jobTable.Any(x => x.Name.ToString().Replace(" ", "") == row[9].ToString().Replace(" ", "")))
                return;

            Data.Job job = new Data.Job();
            job.Name = row[9].ToString();

            jobTable.InsertOnSubmit(job);

            context.SubmitChanges();
        }

        public void CreateRel(DataRow row)
        {
            var org = orgTable.Single(y => y.Name.ToString() == row[6].ToString());

            var dep = depTable.Single(x => x.Name.ToString() == row[8].ToString() && x.OrganizationId == org.Id);
            var empl = emplTable.Single(x => x.Name.ToString() == row[2].ToString() && x.Patronymic.ToString() == row[3].ToString() && x.Surname.ToString() == row[1].ToString());
            var job = jobTable.Single(x => x.Name.ToString().Replace(" ", "") == row[9].ToString().Replace(" ", ""));
            
            if (relTable.Any(x => x.JobId == job.Id && x.EmployeeId == empl.Id && x.DepartmentId == dep.Id)) return;

            Data.ConnectionJobEmplDep rel = new Data.ConnectionJobEmplDep();
            rel.DepartmentId = dep.Id;
            rel.EmployeeId = empl.Id;
            rel.JobId = job.Id;

            relTable.InsertOnSubmit(rel);

            context.SubmitChanges();
        }
    }
}
